package com.mmy.dao.restaurant;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.restaurant.TRestaurantPlan;

public interface TRestaurantPlanRepository extends JpaRepository<TRestaurantPlan, Integer> {
	/**
	 * 获取计划单最大值
	 * 
	 * @return 返回值说明 最大计划单编码
	 * @author 创建人 dingxd
	 * @date 创建日期 2020.11.16
	 */
	@Query(value = "SELECT MAX(iPlanID) FROM tRestaurantPlan WHERE LEFT(iPlanID,2) = RIGHT(YEAR(NOW()),2)", nativeQuery = true)
	Object getMaxPlanID();

	/*
	 * 新建餐馆计划
	 */
	@Transactional
	@Modifying
	@Query(value = "INSERT INTO mmy.tRestaurantPlan(iPlanID, cService, iRestaurantID, dStartDate, dEndDate, fFloor, fPeer, fPrice, iNumber, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (?1, NULL, 0, NULL, NULL, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);", nativeQuery = true)
	void save(int planID);

	/* 通过ID查找 */
	@Query(value = "select *  from tRestaurantPlan where iplanID=?1", nativeQuery = true)
	TRestaurantPlan findByPlanID(int planID);

	/* 删除计划 */
	@Transactional
	@Modifying
	@Query(value = "delete from tRestaurantPlan where iPlanID in (:lstPlanID)", nativeQuery = true)
	void delRestaurantPlan(@Param("lstPlanID") List<Integer> lstPlanID);

	/**
	 * 前台景区菜单首页显示查询计划日期大于当前日期的景区ID
	 * 
	 * @author 创建人 hanxl
	 * @param strScenicPrice2
	 * @param strScenicPrice1
	 * @date 创建日期 2021.01.13
	 */
	@Query(value = "select distinct iRestaurantID from  tRestaurantPlan where now() between dStartDate and dEndDate and (:strRestPrice1 = \"\" or :strRestPrice1<=fPrice) and (:strRestPrice2 = \"\" or :strRestPrice2>=fPrice) ", nativeQuery = true)
	List<Integer> getRestID(@Param("strRestPrice1") String strRestPrice1, @Param("strRestPrice2") String strRestPrice2);

}
